PDO Driver¶

Linux¶

Requirements

• Operating system: 32-bit or 64-bit Linux
• Web server: Apache
• PHP: 5.2 or 5.3 ( http://php.net/downloads.php )

Installing CUBRID PHP Driver using PECL

If PECL package has been installed on your system, the installation of CUBRID PDO driver is straightforward. PECL will download and compile the driver for you. If you do not have PECL installed, follow the instructions at http://www.cubrid.org/wiki_apis/entry/installing-cubrid-php-driver-using-pecl to get it installed.

1. Enter the following command to install the latest version of CUBRID PDO driver.

   sudo pecl install pdo_cubrid
   

   If you need earlier versions of the driver, you can install exact versions as follows:

   sudo pecl install pdo_cubrid-8.3.1.0003
   

   During the installation, you will be prompted to enter CUBRID base install dir autodetect :. Just to make sure your installation goes smoothly, enter the full path to the directory where you have CUBRID installed. For example, if CUBRID has been installed at /home/cubridtest/CUBRID, then enter /home/cubridtest/CUBRID.

2. Edit the configuration file.

   • If you are using CentOS 6.0 and later or Fedora 15 and later, create a file named pdo_cubrid.ini, enter a command line extension=pdo_cubrid.so, and store the file in the /etc/php.d directory.

   • If you are using earlier versions of Cent0S or Fedora 15, edit the php.ini file (default location: /etc/php5/apache2 or /etc/) and add the following two command lines at the end of the file.

     [CUBRID]
     extension=pdo_cubrid.so
     

3. Restart the web server to apply changes.

Windows¶

Requirements

• Operating system: 32-bit or 64-bit Windows
• Web server: Apache or IIS
• PHP: 5.2 or 5.3 ( http://windows.php.net/download )

Downloading and Installing Compiled CUBRID PDO Driver

First, download CUBRID PHP/PDO driver of which versions match the versions of your operating system and PHP installed at http://www.cubrid.org/?mid=downloads&item=php_driver&os=windows&ostype=any&php=any&driver_type=pdo.

After you download the driver, you will see the php_cubrid.dll file for CUBRID PHP driver or the php_pdo_cubrid.dll file for CUBRID PDO driver. Follow the steps below to install it.

1. Copy this driver to the default PHP extensions directory (usually located at C:\Program Files\PHP\ext).

2. Set your system environment. Check if the environment variable PHPRC is C:\Program Files\PHP and system variable path is added with %PHPRC% and %PHPRC%\ext.

3. Edit php.ini (C:\Program Files\PHP\php.ini) and add the following two lines at the end of the php.ini file.

   [PHP_PDO_CUBRID]
   extension=php_pdo_cubrid.dll
   

   For CUBRID PHP driver, add command lines below.

   [PHP_PDO_CUBRID]
   extension = php_pdo_cubrid.dll
   

4. Restart your web server to apply changes.

Data Source Name (DSN)¶

The PDO_CUBRID data source name (DSN) consists of the following elements:

Example

"cubrid:host=127.0.0.1;port=33000;dbname=demodb"

Predefined Constants¶

The constants defined by CUBRID PDO driver are available only when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using PDO driver. Using driver-specific attributes with another driver may result in unexpected behavior.

The PDO::getAttribute() function may be used to obtain the PDO_ATTR_DRIVER_NAME attribute value to check the driver if your code can run.

The constants below can be used with the PDO::cubrid_schema function to get schema information.

Verifying CUBRID PDO Driver Version¶

If you want to verify that the CUBRID PDO driver is accessible, you can use the PDO::getAvailableDrivers () function.

<?php
echo'PDO Drivers available:
';
foreach(PDO::getAvailableDrivers()as $driver)
{
if($driver =="cubrid"){
echo" - Driver: <b>".$driver.'</b>
';
}else{
echo" - Driver: ".$driver.'
';
}
}
?>

This script will output all the currently installed PDO drivers:

PDO Drivers available:
- Driver: mysql
- Driver: pgsql
- Driver: sqlite
- Driver: sqlite2
- Driver: cubrid

Connecting to CUBRID¶

Use the data source name (DSN) to connect to the database server. For details about DSN, see Data Source Name (DSN).

Below is a simple PHP example script which performs a PDO connection to the CUBRID demodb database. You can notice that errors are handling in PDO by using a try-catch mechanism and the connection is closed by assigning NULL to the connection object.

<?php
$database ="demodb";
$host ="localhost";
$port ="30000";//use default value
$username ="dba";
$password ="";

try{
//cubrid:host=localhost;port=33000;dbname=demodb
$conn_str ="cubrid:dbname=".$database.";host=".$host.";port=".$port;
echo"PDO connect string: ".$conn_str."
";
$db =new PDO($conn_str, $username, $password );
echo"PDO connection created ok!"."
";
$db = null;//disconnect
}catch(PDOException $e){
echo"Error: ".$e->getMessage()."
";
}
?>

If connection succeeds, the output of this script is as follows:

PDO connect string: cubrid:dbname=demodb;host=localhost;port=30000
PDO connection created ok!

Executing a SELECT Statement¶

In PDO, there is more than one way to execute SQL queries.

• Using the query () function
• Using prepared statements (see prepare ()/ execute ()) functions)
• Using the exec () function

The example script below shows the simplest one - using the query () function. You can retrieve the return values from the resultset (a PDOStatement object) by using the column names, like $rs["column_name"].

Note that when you use the query () function, you must ensure that the query code is properly escaped. For information about escaping, see PDO::quote () function.

<?php
include("_db_config.php");
include("_db_connect.php");

$sql ="SELECT * FROM code";
echo"Executing SQL: <b>".$sql.'</b>
';
echo'
';

try{
foreach($db->query($sql)as $row){
echo $row['s_name'].' - '. $row['f_name'].'
';
}
}catch(PDOException $e){
echo $e->getMessage();
}

$db = null;//disconnect
?>

The output of the script is as follows:

Executing SQL: SELECT * FROM code

X - Mixed
W - Woman
M - Man
B - Bronze
S - Silver
G - Gold

Executing an UPDATE Statement¶

The following example shows how to execute an UPDATE statement by using a prepared statement and parameters. You can use the exec () function as an alternative.

<?php
include("_db_config.php");
include("_db_connect.php");

$s_name ='X';
$f_name ='test';
$sql ="UPDATE code SET f_name=:f_name WHERE s_name=:s_name";

echo"Executing SQL: <b>".$sql.'</b>
';
echo'
';

echo":f_name: <b>".$f_name.'</b>
';
echo'
';
echo":s_name: <b>".$s_name.'</b>
';
echo'
';

$qe = $db->prepare($sql);
$qe->execute(array(':s_name'=>$s_name,':f_name'=>$f_name));

$sql ="SELECT * FROM code";
echo"Executing SQL: <b>".$sql.'</b>
';
echo'
';

try{
foreach($db->query($sql)as $row){
echo $row['s_name'].' - '. $row['f_name'].'
';
}
}catch(PDOException $e){
echo $e->getMessage();
}

$db = null;//disconnect
?>

The output of the script is as follows:

Executing SQL: UPDATE code SET f_name=:f_name WHERE s_name=:s_name

:f_name: test

:s_name: X

Executing SQL: SELECT * FROM code

X - test
W - Woman
M - Man
B - Bronze
S - Silver
G - Gold

Using prepare and bind¶

Prepared statements are one of the major features offered by PDO and you can take following benefits by using them.

• SQL prepared statements need to be parsed only once even if they are executed multiple times with different parameter values. Therefore, using a prepared statement minimizes the resources and ,in general, the prepared statements run faster.
• It helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters; however, if other parts of the SQL query are being built up with unescaped input, SQL injection is still possible.

The example script below shows how to retrieve data by using a prepared statement.

<?php
include("_db_config.php");
include("_db_connect.php");

$sql ="SELECT * FROM code WHERE s_name NOT LIKE :s_name";
echo"Executing SQL: <b>".$sql.'</b>
';

$s_name ='xyz';
echo":s_name: <b>".$s_name.'</b>
';

echo'
';

try{
$stmt = $db->prepare($sql);

$stmt->bindParam(':s_name', $s_name, PDO::PARAM_STR);
$stmt->execute();

$result = $stmt->fetchAll();
foreach($result as $row)
{
echo $row['s_name'].' - '. $row['f_name'].'
';
}
}catch(PDOException $e){
echo $e->getMessage();
}
echo'
';

$sql ="SELECT * FROM code WHERE s_name NOT LIKE :s_name";
echo"Executing SQL: <b>".$sql.'</b>
';

$s_name ='X';
echo":s_name: <b>".$s_name.'</b>
';

echo'
';

try{
$stmt = $db->prepare($sql);

$stmt->bindParam(':s_name', $s_name, PDO::PARAM_STR);
$stmt->execute();

$result = $stmt->fetchAll();
foreach($result as $row)
{
echo $row['s_name'].' - '. $row['f_name'].'
';
}
$stmt->closeCursor();
}catch(PDOException $e){
echo $e->getMessage();
}
echo'
';

$db = null;//disconnect
?>

The output of the script is as follows:

Executing SQL: SELECT * FROM code WHERE s_name NOT LIKE :s_name
:s_name: xyz

X - Mixed
W - Woman
M - Man
B - Bronze
S - Silver
G - Gold

Executing SQL: SELECT * FROM code WHERE s_name NOT LIKE :s_name
:s_name: X

W - Woman
M - Man
B - Bronze
S - Silver
G - Gold

Using the PDO::getAttribute() Function¶

The PDO::getAttribute () function is very useful to retrieve the database connection attributes. For example,

• Driver name
• Database version
• Auto-commit state
• Error mode

Note that if you want to set attributes values (assuming that they are writable), you should use the PDO::setAttribute function.

The following example script shows how to retrieve the current versions of client and server by using the PDO::getAttribute () function.

<?php
include("_db_config.php");
include("_db_connect.php");

echo"Driver name: <b>".$db->getAttribute(PDO::ATTR_DRIVER_NAME)."</b>";
echo"
";
echo"Client version: <b>".$db->getAttribute(PDO::ATTR_CLIENT_VERSION)."</b>";
echo"
";
echo"Server version: <b>".$db->getAttribute(PDO::ATTR_SERVER_VERSION)."</b>";
echo"
";

$db = null;//disconnect
?>

The output of the script is as follows:

Driver name: cubrid
Client version: 8.3.0
Server version: 8.3.0.0337

CUBRID PDO Extensions¶

In CUBRID, the PDO::cubrid_schema() function is offered as an extension; the function is used to retrieve the database schema and metadata information. Below is an example script that returns information about primary key for the nation table by using this function.

<?php
include("_db_config.php");
include("_db_connect.php");
try{
echo"Get PRIMARY KEY for table: <b>nation</b>:

";
$pk_list = $db->cubrid_schema(PDO::CUBRID_SCH_PRIMARY_KEY,"nation");
print_r($pk_list);
}catch(PDOException $e){
echo $e->getMessage();
}

$db = null;//disconnect
?>

The output of the script is as follows:

Get PRIMARY KEY for table: nation:
Array ( [0] => Array ( [CLASS_NAME] => nation [ATTR_NAME] => code [KEY_SEQ] => 1 [KEY_NAME] => pk_nation_code ) )